package com.xmy.cultivate.mapper;

import com.baomidou.mybatisplus.core.conditions.Wrapper;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.core.toolkit.Constants;
import com.xmy.cultivate.entity.OrderDetail;
import com.xmy.cultivate.entity.StudentFaceData;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.xmy.cultivate.entity.views.ClashFace;
import com.xmy.cultivate.entity.views.ResStudentFace;
import org.apache.ibatis.annotations.*;

import java.util.List;
import java.util.Map;

/**
 * <p>
 *  Mapper 接口
 * </p>
 *
 * @author min
 * @since 2023-01-04
 */
public interface StudentFaceDataMapper extends BaseMapper<StudentFaceData> {

    @Select("select * from student_face_data where student_id <> entity_id and update_level>0")
    public List<StudentFaceData> getDataForStudentIdNeEntityId();


    @Select({"<script>",
            "SELECT",
                      "*,",
                      "getSchoolName ( school_id ) AS school_name ",
                    "FROM",
                      "(",
                      "SELECT",
                        "t1.*,",
                        "t3.NAME AS student_name ",
                      "FROM",
                        "( SELECT id, school_id, student_id FROM order_detail where 1=1",
                        "<when test='schoolIdStr!=null and schoolIdStr!=&apos;&apos; '>",
                        "  and school_id in(${schoolIdStr})",
                        "</when>",
                        "<when test='yearPart!=null and yearPart!=&apos;&apos; '>",
                        "  and year_part = ${yearPart}",
                        "</when>",
                        "<when test='quarterNum!=null and quarterNum!=&apos;&apos; '>",
                        "  and quarter_num = ${quarterNum}",
                        "</when>",
                        " GROUP BY student_id ) AS t1",
                      "LEFT JOIN student AS t3 ON t1.student_id = t3.id ",
                      ") AS t",
                       "where 1=1",
                        "<when test='studentName!=null and studentName !=&apos;&apos; '>",
                        "  and student_name like '%${studentName}%'",
                        "</when>",
                        "order by id desc",
            "</script>"
    })
    @Results({
            @Result(column = "id",property = "id"),
            @Result(
                    property = "studentFaceDataList",
                    column = "{schoolId=school_id ,studentId=student_id}",
                    many = @Many(select = "com.xmy.cultivate.mapper.StudentFaceDataMapper.getStudentFaceManyList")
            )
    })
    public abstract IPage<ResStudentFace> getstudentFaceList(IPage<?> page,
                                                             @Param("yearPart") String yearPart,
                                                             @Param("quarterNum") String quarterNum,
                                                             @Param("schoolIdStr") String schoolIdStr,
                                                             @Param("isSigin") String isSigin,
                                                             @Param("studentName") String studentName);

    @Select("select * from student_face_many_detail where student_id =${studentId} and deleted=0 and ali_account in(select ali_account from organization where id =${schoolId})")
    public abstract List<StudentFaceData> getStudentFaceManyList(@Param("schoolId") Long schoolId, @Param("studentId") Long studentId);


    @Select({"<script>",
            "SELECT",
                    "* ",
                    "FROM",
                    "(",
                    "SELECT",
                      "t1.deleted,",
                      "t1.entity_id,",
                      "t1.ali_account,",
                      "t2.id as student_id,",
                      "t3.id as clash_student_id,",
                      "t2.NAME AS student_name,",
                      "t3.NAME AS clash_student_name ",
                    "FROM",
                      "( SELECT * FROM student_face_many_detail WHERE student_id &lt;&gt; entity_id ) AS t1",
                      "LEFT JOIN student AS t2 ON t1.student_id = t2.id",
                      "LEFT JOIN student AS t3 ON t1.entity_id = t3.id ",
                    ") AS t ",
                    "WHERE",
                    "deleted =0",
                    "<when test='yearPart!=null and yearPart!=&apos;&apos; and quarterNum!=null and quarterNum!=&apos;&apos; '>",
                        "and student_id in(select student_id from order_apply where year_part = ${yearPart} and quarter_num = ${quarterNum} and `status`&lt;&gt; 2 GROUP BY student_id)",
                    "</when>",
                    "<when test='schoolIdStr!=null and schoolIdStr!=&apos;&apos; '>",
                    "  and ali_account in(select ali_account from organization where id in(${schoolIdStr}))",
                    "</when>",
                    "<when test='studentName!=null and studentName!=&apos;&apos; '>",
                    "  and student_name like '%${studentName}%'",
                    "</when>",
                    "<when test='isEqName!=null and isEqName!=&apos;&apos; and isEqName==0'>",
                        "and student_name &lt;&gt; clash_student_name ",
                    "</when>",
                    "GROUP BY",
                    "ali_account,",
                    "student_id,",
                    "entity_id ",
                    "ORDER BY",
                    "ali_account ASC",
            "</script>"})
    @Results({
            @Result(column = "id",property = "id"),
            @Result(
                    property = "studentFaceDataList",
                    column = "{aliAccount=ali_account ,studentId=student_id}",
                    many = @Many(select = "com.xmy.cultivate.mapper.StudentFaceDataMapper.getStudentFaceManyForAlliAccountList")
            ),
            @Result(
                    property = "clashStudentFaceDataList",
                    column = "{aliAccount=ali_account ,studentId=clash_student_id}",
                    many = @Many(select = "com.xmy.cultivate.mapper.StudentFaceDataMapper.getStudentFaceManyForAlliAccountList")
            )
    })
    public abstract IPage<ClashFace> getClashStudentList(IPage<?> page,
                                                         @Param("yearPart") String yearPart,
                                                         @Param("quarterNum") String quarterNum,
                                                         @Param("schoolIdStr") String schoolIdStr,
                                                         @Param("studentName") String studentName,
                                                         @Param("isEqName") String isEqName);

    @Select("select * from student_face_many_detail where student_id =${studentId} and deleted=0 and ali_account = '${aliAccount}' ")
    public abstract List<StudentFaceData> getStudentFaceManyForAlliAccountList(@Param("aliAccount") Long aliAccount, @Param("studentId") Long studentId);




    /**
     * 获取还在读的人脸数据
     * @return
     */
    @Select({"<script>",
            "SELECT",
                    "* ",
                    "FROM",
                    "(",
                    "SELECT",
                      "t1.* ",
                    "FROM",
                      "(",
                      "SELECT",
                      "* ",
                      "FROM",
                      "student_face_data ",
                      "WHERE",
                      "id IN ( SELECT MAX( id ) AS id FROM student_face_data WHERE deleted = 0 GROUP BY student_id )) AS t1",
                    "INNER JOIN ( SELECT * FROM order_detail WHERE year_part = 2024 AND `quarter_num` = 2 AND all_course_count &gt; 0 AND `status` &lt;&gt; 2 GROUP BY student_id ) AS t2 ON t1.student_id = t2.student_id ",
                    ") AS t where oss_image not in (select oss_image from student_face_many_detail)",
            "<when test='updateLevel!=null and updateLevel!=&apos;&apos; '>",
            "  where update_level = ${updateLevel}",
            "</when>",
            "<when test='studentId!=null and studentId!=&apos;&apos; '>",
            "  where student_id = ${studentId}",
            "</when>",
            "ORDER BY school_id asc",
            "limit ${pageSize}",
            "</script>"})
    public abstract List<StudentFaceData> getAtReadingStudent(@Param("studentId") String studentId,@Param("updateLevel") String updateLevel,@Param("pageSize") Integer pageSize);
}
